-- table Stock
create sequence stock_sequence;
--alter sequence STOCK_SEQUENCE restart with 1
create table stock (
	id bigint default stock_sequence.nextval,
	symbol varchar(10),
	name varchar(255),
	createdon timestamp,
	modifiedon timestamp,
	modifiedby varchar(50),
	createdby varchar(50)
);

alter table stock  alter column id set not null;
alter table stock  alter column name set not null;
alter table stock  alter column symbol set not null;
alter table stock  add primary key(id);
alter table stock add index varchar(255);
alter table stock add constraint unique_symbol unique(symbol); 
alter table stock  alter column name set null;

-- Table StockDaily
create sequence stock_daily_sequence;
--alter sequence stock_daily_sequence restart with 1
create table StockDaily (
	id bigint default stock_daily_sequence.nextval not null primary key,
	datetime timestamp not null,
	open double not null,
	low double not null,	
	high double not null,
	close double not null,
	percentPriceChange decimal(10,4),
	priceChange double,
	volume bigint,
	averageVolume bigint
);
alter table StockDaily add column stockId bigint not null;
alter table StockDaily add foreign key(stockId) references stock(id);
alter table stockdaily  add symbol  varchar(10);
alter table stockdaily add constraint unique_date unique(datetime); 
alter table stockdaily drop constraint unique_date;
alter table stockdaily add constraint unique_date_symbol unique(datetime,symbol);

-- added 03/14/2015
alter table StockDaily add column price_action varchar(10);

-- table StockWeekly
create sequence stock_weekly_sequence;
--alter sequence stock_weekly_sequence restart with 1
create table StockWeekly (
	id bigint default stock_weekly_sequence.nextval not null primary key,
	--stockId bigint not null foreign key(stockId) references stock(id),
	datetime timestamp not null,
	open double not null,
	low double not null,	
	high double not null,
	close double not null,
	percentPriceChange decimal(10,4),
	priceChange double,
	volume bigint,
	averageVolume bigint
);
alter table StockWeekly add column stockId bigint not null;
alter table StockWeekly add foreign key(stockId) references stock(id);
alter table StockWeekly  add symbol  varchar(10);
alter table StockWeekly add constraint unique_date unique(datetime); 
alter table StockWeekly drop constraint unique_date;
alter table StockWeekly add constraint stockweekly_unique_date_symbol unique(datetime,symbol);


-- table StockMovingAverage
create sequence stock_moving_average_sequence;
--alter sequence stock_moving_average_sequence restart with 1
create table StockMovingAverage (
	id bigint default stock_moving_average_sequence.nextval not null primary key,
	stockId bigint not null,
	sma50 double,
	sma200 double,
	sma20 double,
	ema10 double,
	ema8 double
);
--alter table StockMovingAverage add foreign key(stockId) references stock(id);
alter table stockmovingaverage  add foreign key(stockId) references StockDaily(id);
alter table StockDaily add foreign key(stockId) references stock(id);
alter table stockmovingaverage  drop column ema10 ;
alter table stockmovingaverage  drop column ema8 ;
alter table stockmovingaverage  drop column sma200 ;
alter table stockmovingaverage  drop column sma50 ;
alter table stockmovingaverage  drop column sma20;

alter table stockmovingaverage  add type bigint;
alter table stockmovingaverage  add  period bigint;
alter table stockmovingaverage  add runningsum double;

alter table stockmovingaverage  drop constraint CONSTRAINT_4BE;
alter table stockmovingaverage drop column stockId;
alter table stockmovingaverage  add  stockDailyId bigint;
ALTER TABLE stockmovingaverage ADD CONSTRAINT foriegn_stockdailyid FOREIGN KEY(stockDailyId)  REFERENCES STOCKDAILY(ID);
alter table stockmovingaverage  add  mavalue double;



-- table StockRating
create sequence stock_rating_sequence;
create table StockRating (
	id bigint default stock_rating_sequence.nextval,

	modifiedon timestamp,
	
	Rank double,
	Symbol varchar(10),
	CompanyName varchar(255),
	CompRating int,
	EPSRank int,
	RelSt int,
	GrpStr varchar(10),
	Smr varchar(10),
	AccDis varchar(10),
	SponRating varchar(10),
	Price double,
	PriceClose double,
	PriceChange double,
	VolPerChange int,
	DailyVol int,
	SpecialChar varchar(10),
	WeekHigh52 double,
	PerOffHigh double,
	PERatio double,
	DivYield double,
	LastQtrSalesPerChg double,
	LastQtrEpsPerChg double,
	ConsecQtrEpsGrt15 int,
	CurQtrEpsEstPerChg double,
	CurYrEpsEstPerChg double,
	PretaxMargin double,
	ROE double,
	MgmtOwnsPer int,
	QuoteUrl varchar(100),
	StockCheckupUrl varchar(100),
	MarketsmithUrl varchar(100),
	LeaderboardUrl varchar(100),
	ChartAnalysisUrl varchar(100),
	Ibd100NewEntryFlag  varchar(10),
	Ibd100UpInRankFlag  varchar(10),
	IbdBigCap20NewEntryFlag  varchar(10),
	CompDesc varchar(400)
);
alter table StockRating add column stockId bigint not null;
alter table StockRating add foreign key(stockId) references stock(id);
alter table StockRating add column lastupdateon timestamp;



-- table AnalyticsEvent
create sequence analytics_event_sequence;
create table AnalyticsEvent (
	id bigint default analytics_event_sequence.nextval,
	stockId bigint not null,
	symbol varchar(10) not null,
	stockdailyid bigint not null,	
	eventType varchar(50) not null,
	details varchar(3000),
	createdon timestamp
);

alter table AnalyticsEvent add column rank bigint;
alter table AnalyticsEvent add column 	pricePivotPosition varchar(500);
alter table AnalyticsEvent add column 	riskReward bigint;
alter table AnalyticsEvent alter column 	riskReward double;
alter table AnalyticsEvent add column 	risk double;
alter table AnalyticsEvent add column 	reward double;
alter table AnalyticsEvent add column 	priceAtEvent double;
alter table AnalyticsEvent drop column 	priceAtEvent;
alter table AnalyticsEvent add column 	priceOnEvent double;
alter table AnalyticsEvent add column 	currentPrice double;
alter table AnalyticsEvent add column 	priceChangeSinceEvent double;
alter table AnalyticsEvent add column 	priceChangePercentSinceEvent double;

-----------------------------------------------------------
------------------------INDICES----------------------------
-----------------------------------------------------------
create index idx_stockdailydata_stockid on stockdaily(stockid);
create index idx_stockdailydata_volume on stockdaily(volume);
create index idx_stockdailydata_symbol on stockdaily(symbol);
create index idx_stockdailydata_datetime on stockdaily(datetime);
create index idx_stockdailydata_id on stockdaily(id);
create index idx_stockdailydata_priceaction on stockdaily(price_action);

create index idx_stockweeklydata_stockid on StockWeekly(stockid);
create index idx_stockweeklydata_volume on StockWeekly(volume);
create index idx_stockweeklydata_symbol on StockWeekly(symbol);
create index idx_stockweeklydata_datetime on StockWeekly(datetime);
create index idx_stockweeklydata_id on StockWeekly(id);



create index idx_stock_symbol on stock(symbol);


create index idx_stockrating_stockid on stockrating(stockid);
create index idx_stockrating_RelSt on stockrating(RelSt);
create index idx_stockrating_CompRating on stockrating(CompRating);
create index idx_stockrating_Smr on stockrating(Smr);
create index idx_stockrating_AccDis on stockrating(accdis);
create index idx_stockrating_EPSRank on stockrating(EPSRank);
create index idx_stockrating_GrpStr on stockrating(GrpStr);
create index idx_stockrating_SponRating on stockrating(SponRating);
create index idx_stockrating_SponRating on stockrating(SponRating);


create index idx_analyticsevent_stockid on analyticsevent(stockid);
create index idx_analyticsevent_symbol on analyticsevent(symbol);
create index idx_analyticsevent_eventtype on analyticsevent(eventtype);
create index idx_analyticsevent_createdon on analyticsevent(createdon);
create index idx_analyticsevent_rank on analyticsevent(rank);
create index idx_analyticsevent_riskreward on analyticsevent(riskReward);

-----------------------------------------------------------
-----------------------------------------------------------
--------------------- CLEANUP -----------------------------
-----------------------------------------------------------
delete from stockmovingaverage ;
alter sequence stock_moving_average_sequence restart with 1;

delete from stockdaily; 
alter sequence stock_daily_sequence restart with 1;

delete from stockweekly; 
alter sequence stock_weekly_sequence restart with 1;

delete from stock;
alter sequence STOCK_SEQUENCE restart with 1;


delete from analyticsevent ;
alter sequence ANALYTICS_EVENT_SEQUENCE restart with 1;
------------------------------------------------------------


-----------------------------------------------------------
--------------------- COUNT -------------------------------
-----------------------------------------------------------
select count(*) from stock;
select count(*) from stockdaily ;
select count(*) from stockweekly;
select count(*) from stockmovingaverage ;
-----------------------------------------------------------



#!/bin/sh
dir=$(dirname "$0")
#java -cp "$dir/h2-1.3.175.jar:$H2DRIVERS:$CLASSPATH" org.h2.tools.Console "$@" 
java -cp "$dir/h2-1.3.175.jar:$H2DRIVERS:$CLASSPATH" org.h2.tools.Script -url jdbc:h2:tcp://localhost/home/guru/dev/LN/db-data/LNDB -user guru -password password -script /tmp/LNDB.zip -options compression zip


#!/bin/sh
dir=$(dirname "$0")
#java -cp "$dir/h2-1.3.175.jar:$H2DRIVERS:$CLASSPATH" org.h2.tools.Console "$@" 
java -cp "$dir/h2-1.3.175.jar:$H2DRIVERS:$CLASSPATH" org.h2.tools.Script -url jdbc:h2:tcp://localhost/home/guru/dev/LN/db-data/LNDB -user guru -password password -script /tmp/LNDB.zip -options compression zip
java -cp "$dir/h2-1.3.175.jar:$H2DRIVERS:$CLASSPATH" org.h2.tools.RunScript -url jdbc:h2:~/test -url jdbc:h2:tcp://localhost/home/guru/dev/LN/db-data/LNDB -user guru -password password -script /tmp/LNDB.zip -options compression zip
